﻿using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Xml.Linq;
using ODM.Data.Common;
using ODM.Data.Model;

namespace ODM.Data.Odm11
{
    public sealed class Odm11DataSource : DataSource, IDisposable
    {
        private Odm11Entities _data;

        public Odm11DataSource()
        {
            _data = new Odm11Entities();
        }

        #region DataValue operations

        public override ODM.Data.Model.DataValue GetDataValue(int ID)
        {
            var result = from dv in this._data.DataValues where dv.ID == ID select dv;

            return GetDataValues(result).FirstOrDefault();
        }

        /// <summary>
        /// Gets the data values.
        /// </summary>
        /// <param name="seriesID">The series ID.</param>
        /// <returns></returns>
        public override IEnumerable<ODM.Data.Model.DataValue> GetDataValues(int seriesID)
        {
            var seriesCatalogs = from sc in this._data.SeriesCatalog where sc.SeriesID == seriesID select sc;
            var dataValues = from dv in this._data.DataValues select dv;

            var result = from dv in dataValues
                         from sc in seriesCatalogs
                         where
                             dv.Site.ID == sc.Site.ID
                             && dv.Source.ID == sc.Source.ID
                             && dv.Variable.ID == sc.Variable.ID
                         select dv;

            return GetDataValues(result);
        }

        /// <summary>
        /// Gets the data values.
        /// </summary>
        /// <param name="seriesID">The series ID.</param>
        /// <param name="startDate">The start date.</param>
        /// <param name="endDate">The end date.</param>
        /// <returns></returns>
        public override IEnumerable<ODM.Data.Model.DataValue> GetDataValues(int seriesID, DateTime startDate, DateTime endDate)
        {
            var seriesCatalogs = from sc in this._data.SeriesCatalog where sc.SeriesID == seriesID select sc;

            var result = from dv in this._data.DataValues
                         from sc in seriesCatalogs
                         where
                             dv.Site.ID == sc.Site.ID
                             && dv.Source.ID == sc.Source.ID
                             && dv.Variable.ID == sc.Variable.ID
                             && dv.Method.ID == sc.Method.ID
                             && dv.QualityControlLevel.ID == sc.QualityControlLevel.ID
                             && dv.LocalDateTime >= startDate
                             && dv.LocalDateTime <= endDate
                         select dv;

            return GetDataValues(result);
        }

        public override IEnumerable<ODM.Data.Model.DataValue> GetDataValues(int siteId, int variableId, int qualityControlLevelId, int methodId, int sourceId)
        {
            var result = from dv in this._data.DataValues
                         where
                             dv.Site.ID == siteId
                             && dv.Variable.ID == variableId
                             && dv.QualityControlLevel.ID == qualityControlLevelId
                             && dv.Method.ID == methodId
                             && dv.Source.ID == sourceId
                         select dv;

            return GetDataValues(result);
        }

        public override ODM.Data.Model.DataValue CreateDataValue(ODM.Data.Model.DataValue value)
        {
            var dataValue = this._data.AddDataValue(value.Value,
                value.ValueAccuracy,
                value.DateTime,
                value.UTCOffset,
                value.UTCDateTime,
                value.Site.ID,
                value.Variable.ID,
                value.OffsetValue,
                (value.OffsetType == null) ? null : (int?)value.OffsetType.ID,
                value.CensorCode,
                (value.Qualifier == null) ? null : (int?)value.Qualifier.ID,
                value.Method.ID,
                value.Source.ID,
                (value.Sample == null) ? null : (int?)value.Sample.ID,
                null,
                value.QualityControlLevel.ID).AsQueryable().FirstOrDefault();

            var result = GetDataValue(dataValue.ID);

            return result;
        }

        public override ODM.Data.Model.DataValue UpdateDataValue(ODM.Data.Model.DataValue dataValue)
        {
            var dv = (from x in this._data.DataValues where x.ID == dataValue.ID select x).FirstOrDefault();
            if (dv != null)
            {
                dv.Value = dataValue.Value;
                dv.ValueAccuracy = dataValue.ValueAccuracy;
                dv.LocalDateTime = dataValue.DateTime;
                dv.UTCOffset = dataValue.UTCOffset;
                dv.DateTimeUTC = dataValue.DateTime.AddHours(dataValue.UTCOffset);
                dv.OffsetValue = dataValue.OffsetValue;
                dv.CensorCode = dataValue.CensorCode;
                dv.DerivedFromID = dataValue.DerivedFromValueID;

                this._data.SaveChanges();

                var updatedDataValue = from udv in this._data.DataValues where udv.ID == dataValue.ID select udv;

                return this.GetDataValues(updatedDataValue).FirstOrDefault();
            }

            return null;
        }

        public override bool DeleteDataValue(ODM.Data.Model.DataValue value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the data values.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.DataValue> GetDataValues(IQueryable<DataValue> result)
        {
            var query = result as ObjectQuery<DataValue>;

            var a = from dv in query.Include("Method")
                                        .Include("OffsetType")
                                        .Include("Qualifier")
                                        .Include("QualityControlLevel")
                                        .Include("Sample")
                                        .Include("Site")
                                        .Include("Source")
                                        .Include("Variable")
                                        .Include("Variable.Unit")
                                        .Include("Variable.TimeUnits")
                    where
                        dv.Value != dv.Variable.NoDataValue
                    orderby dv.LocalDateTime
                    select dv;

            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var dv in a)
            {
                yield return new ODM.Data.Model.DataValue
                {
                    CensorCode = dv.CensorCode,
                    DateTime = dv.DateTimeUTC,
                    DerivedFromValueID = dv.DerivedFromID,
                    Method = (dv.Method == null) ? null : new ODM.Data.Model.Method
                    {
                        ID = dv.Method.ID,
                        Description = dv.Method.Description,
                    },
                    OffsetType = (dv.OffsetType == null) ? null : new ODM.Data.Model.OffsetType
                    {
                        ID = dv.OffsetType.ID,
                        Description = dv.OffsetType.Description,
                        Unit = new ODM.Data.Model.Unit
                        {
                            ID = dv.OffsetType.Unit.ID,
                            Name = dv.OffsetType.Unit.Type,
                            Type = dv.OffsetType.Unit.Type,
                            Abbreviation = dv.OffsetType.Unit.Abbreviation,
                        }
                    },
                    OffsetValue = dv.OffsetValue,
                    Qualifier = (dv.Qualifier == null) ? null : new ODM.Data.Model.Qualifier
                    {
                        ID = dv.Qualifier.ID,
                        Code = dv.Qualifier.Code,
                        Description = dv.Qualifier.Description,
                    },
                    QualityControlLevel = (dv.QualityControlLevel == null) ? null : new ODM.Data.Model.QualityControlLevel
                    {
                        ID = dv.QualityControlLevel.ID,
                        Code = dv.QualityControlLevel.Code,
                        Name = dv.QualityControlLevel.Definition,
                    },
                    Sample = (dv.Sample == null) ? null : new ODM.Data.Model.Sample
                    {
                        ID = dv.Sample.ID,
                        LaboratoryCode = dv.Sample.LaboratoryCode,
                        Type = dv.Sample.Type,
                    },
                    Site = (dv.Site == null) ? null : new ODM.Data.Model.Site
                    {
                        ID = dv.Site.ID,
                        Code = dv.Site.Code,
                        Name = dv.Site.Name,
                    },
                    Source = (dv.Source == null) ? null : new ODM.Data.Model.Source
                    {
                        ID = dv.Source.ID,
                        Description = dv.Source.Description,
                        Organization = dv.Source.Organization,
                        Citation = dv.Source.Citation,
                    },
                    UTCOffset = dv.UTCOffset,
                    Value = dv.Value,
                    ValueAccuracy = dv.ValueAccuracy,
                    ID = dv.ID,
                    Variable = (dv.Variable == null) ? null : new ODM.Data.Model.Variable
                    {
                        ID = dv.Variable.ID,
                        Code = dv.Variable.Code,
                        Name = dv.Variable.Name,
                        Unit = (dv.Variable.Unit == null) ? null : new ODM.Data.Model.Unit
                        {
                            ID = dv.Variable.Unit.ID,
                            Name = dv.Variable.Unit.Type,
                            Type = dv.Variable.Unit.Type,
                            Abbreviation = dv.Variable.Unit.Abbreviation,
                        },
                        Speciation = dv.Variable.Speciation,
                        GeneralCategory = dv.Variable.GeneralCategory,
                        DataType = dv.Variable.DataType,
                        ValueType = dv.Variable.ValueType,
                        SampleMedium = dv.Variable.SampleMedium,
                        IsRegular = dv.Variable.IsRegular,
                        NoDataValue = dv.Variable.NoDataValue,
                        TimeSupport = (dv.Variable.TimeUnits == null) ? null : new UnitValue<double>
                        {
                            ID = dv.Variable.TimeUnits.ID,
                            Name = dv.Variable.TimeUnits.Name,
                            Type = dv.Variable.TimeUnits.Type,
                            Abbreviation = dv.Variable.TimeUnits.Abbreviation,
                            Value = dv.Variable.TimeSupport,
                        },
                    },
                };
            }
        }

        #endregion

        #region Method operations

        public override ODM.Data.Model.Method GetMethod(int ID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the method.
        /// </summary>
        /// <param name="description">The description.</param>
        /// <returns></returns>
        public override ODM.Data.Model.Method GetMethod(string description)
        {
            var result = from qcl in this._data.Methods
                         where qcl.Description == description
                         select qcl;

            return GetMethods(result).FirstOrDefault();
        }

        public override IEnumerable<ODM.Data.Model.Method> GetMethods()
        {
            var result = from qcl in this._data.Methods
                         select qcl;

            return GetMethods(result);
        }

        public override ODM.Data.Model.Method CreateMethod(ODM.Data.Model.Method value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Method UpdateMethod(ODM.Data.Model.Method value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteMethod(ODM.Data.Model.Method value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the data values.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.Method> GetMethods(IQueryable<Method> result)
        {
            var a = from qcl in result
                    select new ODM.Data.Model.Method
                    {
                        ID = qcl.ID,
                        Description = qcl.Description,
                    }; ;

            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region OffsetType operations

        public override ODM.Data.Model.OffsetType GetOffsetType(int ID)
        {
            var result = from ot in this._data.OffsetTypes
                         where ot.ID == ID
                         select ot;

            return GetOffsetTypes(result).FirstOrDefault();
        }

        public override IEnumerable<ODM.Data.Model.OffsetType> GetOffsetTypes()
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.OffsetType CreateOffsetType(ODM.Data.Model.OffsetType value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.OffsetType UpdateOffsetType(ODM.Data.Model.OffsetType value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteOffsetType(ODM.Data.Model.OffsetType value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the offset types.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.OffsetType> GetOffsetTypes(IQueryable<OffsetType> result)
        {
            var a = from ot in result
                    select new ODM.Data.Model.OffsetType
                    {
                        ID = ot.ID,
                        Description = ot.Description,
                        Unit = new ODM.Data.Model.Unit
                        {
                            ID = ot.Unit.ID,
                            Name = ot.Unit.Name,
                            Type = ot.Unit.Type,
                            Abbreviation = ot.Unit.Abbreviation,
                        },
                    };

            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region Qualifier operations

        public override ODM.Data.Model.Qualifier GetQualifier(int ID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the qualifier.
        /// </summary>
        /// <param name="code">The code.</param>
        /// <param name="description">The description.</param>
        /// <returns></returns>
        public override ODM.Data.Model.Qualifier GetQualifier(string code, string description)
        {
            var result = from q in this._data.Qualifiers
                         where
                            q.Code == code
                            && q.Description == description
                         select q;
            return GetQualifiers(result).FirstOrDefault();
        }

        public override IEnumerable<ODM.Data.Model.Qualifier> GetQualifiers()
        {
            var result = from qcl in this._data.Qualifiers
                         select qcl;

            return GetQualifiers(result);
        }

        public override ODM.Data.Model.Qualifier CreateQualifier(ODM.Data.Model.Qualifier value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Qualifier UpdateQualifier(ODM.Data.Model.Qualifier value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteQualifier(ODM.Data.Model.Qualifier value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the qualifiers.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.Qualifier> GetQualifiers(IQueryable<Qualifier> result)
        {
            var a = from q in result
                    select new ODM.Data.Model.Qualifier
                    {
                        ID = q.ID,
                        Code = q.Code,
                        Description = q.Description,
                    };


            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region QualityControlLevel operations

        public override ODM.Data.Model.QualityControlLevel GetQualityControlLevel(int ID)
        {
            var result = from qcl in this._data.QualityControlLevels
                         where qcl.ID == ID
                         select qcl;

            return this.GetQualityControlLevels(result).FirstOrDefault();
        }

        /// <summary>
        /// Gets the quality control level.
        /// </summary>
        /// <param name="code">The code.</param>
        /// <param name="definition">The definition.</param>
        /// <returns></returns>
        public override ODM.Data.Model.QualityControlLevel GetQualityControlLevel(string code, string definition)
        {
            var result = from qcl in this._data.QualityControlLevels
                         where qcl.Code == code
                         && qcl.Definition == definition
                         select qcl;

            return this.GetQualityControlLevels(result).FirstOrDefault();
        }

        public override IEnumerable<ODM.Data.Model.QualityControlLevel> GetQualityControlLevels()
        {
            var result = from qcl in this._data.QualityControlLevels
                         select qcl;

            return this.GetQualityControlLevels(result);
        }

        public override ODM.Data.Model.QualityControlLevel CreateQualityControlLevel(ODM.Data.Model.QualityControlLevel value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.QualityControlLevel UpdateQualityControlLevel(ODM.Data.Model.QualityControlLevel value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteQualityControlLevel(ODM.Data.Model.QualityControlLevel value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the data values.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.QualityControlLevel> GetQualityControlLevels(IQueryable<QualityControlLevel> result)
        {
            var a = from qcl in result
                    select new ODM.Data.Model.QualityControlLevel
                    {
                        ID = qcl.ID,
                        Code = qcl.Code,
                        Name = qcl.Definition,
                    };

            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region Sample operations

        public override ODM.Data.Model.Sample GetSample(int ID)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Sample GetSample(string type, string code)
        {
            var result = from s in this._data.Samples
                         where
                            s.Type == type
                            && s.LaboratoryCode == code
                         select s;

            return GetSamples(result).FirstOrDefault();
        }

        public override IEnumerable<ODM.Data.Model.Sample> GetSamples()
        {
            var result = from s in this._data.Samples
                         select s;

            return GetSamples(result);
        }

        public override ODM.Data.Model.Sample CreateSample(ODM.Data.Model.Sample value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Sample UpdateSample(ODM.Data.Model.Sample value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteSample(ODM.Data.Model.Sample value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the samples.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.Sample> GetSamples(IQueryable<Sample> result)
        {
            var a = from s in result
                    select new ODM.Data.Model.Sample
                    {
                        LaboratoryCode = s.LaboratoryCode,
                        Type = s.Type,
                    };


            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region SeriesCatalog operations

        public override ODM.Data.Model.SeriesCatalog GetSeriesCatalog(int ID)
        {
            var result = from sc in this._data.SeriesCatalog where sc.SeriesID == ID select sc;

            return GetSeriesCatalog(result).FirstOrDefault();
        }

        public override ODM.Data.Model.SeriesCatalog GetSeriesCatalog(int siteId, int variableId, int methodId, int sourceId, int qualityControlLevelId)
        {
            var result = from sc in this._data.SeriesCatalog
                         select sc;

            result = result.Where(sc => sc.Site.ID == siteId);
            result = result.Where(sc => sc.Variable.ID == variableId);
            result = result.Where(sc => sc.Method.ID == methodId);
            result = result.Where(sc => sc.Source.ID == sourceId);
            result = result.Where(sc => sc.QualityControlLevel.ID == qualityControlLevelId);

            return GetSeriesCatalog(result).FirstOrDefault();
        }

        public override IEnumerable<ODM.Data.Model.SeriesCatalog> GetSeriesCatalogs()
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the sites.
        /// </summary>
        /// <returns></returns>
        public override IEnumerable<ODM.Data.Model.SeriesCatalog> GetSeriesCatalog(string siteCode, string siteName, string variableCode, string variableName)
        {
            var result = from sc in this._data.SeriesCatalog.Include("QualityControlLevel").Include("Variable").Include("Site").Include("Method").Include("Source")
                         select sc;

            result = result.Where(sc => sc.SiteCode == siteCode);
            result = result.Where(sc => sc.SiteName == siteName);
            result = result.Where(sc => sc.VariableCode == variableCode);
            result = result.Where(sc => sc.VariableName == variableName);

            return GetSeriesCatalog(result);
        }

        /// <summary>
        /// Gets the sites.
        /// </summary>
        /// <returns></returns>
        public override IEnumerable<ODM.Data.Model.SeriesCatalog> GetSeriesCatalog(IEnumerable<string> siteCodes, IEnumerable<string> siteNames, QueryOperator siteOperator, IEnumerable<string> variableCodes, IEnumerable<string> variableNames, QueryOperator variableOperator, IEnumerable<string> methods, IEnumerable<string> sourceOrganizations, IEnumerable<string> sourceDescriptions, QueryOperator sourceOperator, IEnumerable<string> generalCategories, IEnumerable<string> valueTypes, IEnumerable<string> sampleMedium, IEnumerable<string> dataTypes, IEnumerable<int> qcLevels, DateTime? beginDate, DateTime? endDate, QueryOperator observationCountOperator, int? observationCount)
        {
            var result = from sc in this._data.SeriesCatalog.Include("QualityControlLevel").Include("Variable")
                         select sc;

            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.SiteCode.Contains(value))), siteCodes, siteOperator);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.SiteName.Contains(value))), siteNames, siteOperator);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.VariableCode.Contains(value))), variableCodes, variableOperator);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.VariableName.Contains(value))), variableNames, variableOperator);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.MethodDescription.Contains(value))), methods);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.Organization.Contains(value))), sourceOrganizations, sourceOperator);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.SourceDescription.Contains(value))), sourceDescriptions, sourceOperator);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.GeneralCategory.Contains(value))), generalCategories);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.ValueType.Contains(value))), valueTypes);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.SampleMedium.Contains(value))), sampleMedium);
            result = result.Where((Expression<Func<SeriesCatalog, string, bool>>)(((item, value) => item.DataType.Contains(value))), dataTypes);
            result = result.Where((Expression<Func<SeriesCatalog, int, bool>>)(((item, value) => item.QualityControlLevel.ID == value)), qcLevels);

            if (beginDate.HasValue && endDate.HasValue)
            {
                result = result.Where(sc => sc.BeginDateTime > beginDate && sc.EndDateTime < endDate);
            }

            if (observationCount.HasValue)
            {
                switch (observationCountOperator)
                {
                    case QueryOperator.None:
                        break;
                    case QueryOperator.And:
                        break;
                    case QueryOperator.Or:
                        break;
                    case QueryOperator.Equal:
                        result = result.Where(sc => sc.ValueCount == observationCount);
                        break;
                    case QueryOperator.NotEqual:
                        result = result.Where(sc => sc.ValueCount != observationCount);
                        break;
                    case QueryOperator.GreaterThan:
                        result = result.Where(sc => sc.ValueCount > observationCount);
                        break;
                    case QueryOperator.LessThan:
                        result = result.Where(sc => sc.ValueCount < observationCount);
                        break;
                    case QueryOperator.GreaterOrEqualThan:
                        result = result.Where(sc => sc.ValueCount >= observationCount);
                        break;
                    case QueryOperator.LessOrEqualThan:
                        result = result.Where(sc => sc.ValueCount <= observationCount);
                        break;
                    default:
                        break;
                }
            }

            return GetSeriesCatalog(result);
        }

        public override ODM.Data.Model.SeriesCatalog CreateSeriesCatalog(ODM.Data.Model.SeriesCatalog value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.SeriesCatalog UpdateSeriesCatalog(ODM.Data.Model.SeriesCatalog value)
        {
            var seriesCatalog = this._data.UpdateSeriesCatalog(value.ID, null, null, null, null, null, null, null).AsQueryable().FirstOrDefault();
            //var seriesCatalog = (from sc in this._data.SeriesCatalog where sc.SeriesID == value.ID select sc).FirstOrDefault();
            ////  Fake update to cause object to be updated+
            //seriesCatalog.SeriesID = value.ID;
            //this._data.SaveChanges();
            var r = GetSeriesCatalog(seriesCatalog.SeriesID);
            return r;
        }

        public override bool DeleteSeriesCatalog(ODM.Data.Model.SeriesCatalog value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the series catalog.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.SeriesCatalog> GetSeriesCatalog(IQueryable<SeriesCatalog> result)
        {
            var a = from sc in result
                    select new ODM.Data.Model.SeriesCatalog
                    {
                        ID = sc.SeriesID,
                        Variable = new ODM.Data.Model.Variable
                        {
                            ID = sc.Variable.ID,
                            Code = sc.Variable.Code,
                            Name = sc.Variable.Name,
                            DataType = sc.Variable.DataType,
                            GeneralCategory = sc.Variable.GeneralCategory,
                            SampleMedium = sc.Variable.SampleMedium,
                            Speciation = sc.Variable.Speciation,
                            IsRegular = sc.Variable.IsRegular,
                            NoDataValue = sc.Variable.NoDataValue,
                            TimeSupport = new ODM.Data.Model.UnitValue<double>
                            {
                                ID = sc.Variable.TimeUnits.ID,
                                Name = sc.Variable.TimeUnits.Name,
                                Abbreviation = sc.Variable.TimeUnits.Abbreviation,
                                Type = sc.Variable.TimeUnits.Type,
                                Value = sc.Variable.TimeSupport,
                            },
                            Unit = new ODM.Data.Model.Unit
                            {
                                ID = sc.Variable.Unit.ID,
                                Name = sc.Variable.Unit.Name,
                                Abbreviation = sc.Variable.Unit.Abbreviation,
                                Type = sc.Variable.Unit.Type,
                            },
                            ValueType = sc.Variable.ValueType,
                        },
                        QualityControlLevel = new ODM.Data.Model.QualityControlLevel
                        {
                            ID = sc.QualityControlLevel.ID,
                            Code = sc.QualityControlLevel.Code,
                            Name = sc.QualityControlLevel.Definition,
                        },
                        Method = new ODM.Data.Model.Method
                        {
                            ID = sc.Method.ID,
                            Description = sc.MethodDescription,
                        },
                        ValueCount = sc.ValueCount ?? 0,
                        Begin = sc.BeginDateTime,
                        End = sc.EndDateTime,
                        UTCBegin = sc.BeginDateTimeUTC,
                        UTCEnd = sc.EndDateTimeUTC,
                        Source = new ODM.Data.Model.Source
                        {
                            ID = sc.Source.ID,
                            Organization = sc.Organization,
                            Description = sc.SourceDescription,
                            Citation = sc.Citation,
                        },
                        Site = new ODM.Data.Model.Site
                        {
                            ID = sc.Site.ID,
                            Code = sc.SiteCode,
                            Name = sc.SiteName,
                        },
                    };

            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region Site operations

        public override ODM.Data.Model.Site GetSite(int ID)
        {
            throw new NotImplementedException();
        }

        public override IEnumerable<ODM.Data.Model.Site> GetSites()
        {
            var result = from sc in this._data.SeriesCatalog
                         orderby sc.Site.Code, sc.Site.Name
                         group sc by sc.Site into g
                         select g.Key;

            return GetSites(result);
        }

        public override ODM.Data.Model.Site CreateSite(ODM.Data.Model.Site value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Site UpdateSite(ODM.Data.Model.Site value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteSite(ODM.Data.Model.Site value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the sites.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.Site> GetSites(IQueryable<Site> result)
        {
            var a = from r in result
                    orderby r.Code, r.Name
                    select new ODM.Data.Model.Site
                    {
                        ID = r.ID,
                        Code = r.Code,
                        Name = r.Name,
                    };

            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region Source operations

        public override ODM.Data.Model.Source GetSource(int ID)
        {
            throw new NotImplementedException();
        }

        public override IEnumerable<ODM.Data.Model.Source> GetSources()
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Source CreateSource(ODM.Data.Model.Source value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Source UpdateSource(ODM.Data.Model.Source value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteSource(ODM.Data.Model.Source value)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Unit operations

        public override ODM.Data.Model.Unit GetUnit(int ID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the unit.
        /// </summary>
        /// <param name="unitName">Name of the unit.</param>
        /// <returns></returns>
        public override ODM.Data.Model.Unit GetUnit(string unitName)
        {
            var result = from u in this._data.Units
                         where u.Name == unitName
                         select u;

            return GetUnits(result).FirstOrDefault();
        }

        public override IEnumerable<ODM.Data.Model.Unit> GetUnits()
        {
            var result = from u in this._data.Units select u;

            return GetUnits(result);
        }

        public override ODM.Data.Model.Unit CreateUnit(ODM.Data.Model.Unit value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Unit UpdateUnit(ODM.Data.Model.Unit value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteUnit(ODM.Data.Model.Unit value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the variables.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.Unit> GetUnits(IQueryable<Unit> result)
        {
            var a = from u in result
                    select new ODM.Data.Model.Unit
                    {
                        ID = u.ID,
                        Name = u.Name,
                        Type = u.Type,
                        Abbreviation = u.Abbreviation,
                    };

            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region Variable operations

        public override ODM.Data.Model.Variable GetVariable(int ID)
        {
            var result = from v in this._data.Variables
                         where v.ID == ID
                         select v;

            return GetVariables(result).FirstOrDefault();
        }

        /// <summary>
        /// Gets the variable.
        /// </summary>
        /// <param name="variableCode">The variable code.</param>
        /// <returns></returns>
        public override ODM.Data.Model.Variable GetVariable(string variableCode)
        {
            var result = from vb in this._data.Variables
                         where
                             vb.Code == variableCode
                         select vb;

            return GetVariables(result).FirstOrDefault();
        }

        public override IEnumerable<ODM.Data.Model.Variable> GetVariables()
        {
            var result = from v in this._data.Variables
                         select v;

            return GetVariables(result);
        }

        /// <summary>
        /// Gets the variables.
        /// </summary>
        /// <param name="variableName">Name of the variable.</param>
        /// <param name="sampleMedium">The sample medium.</param>
        /// <param name="valueType">Type of the value.</param>
        /// <param name="timeSupport">The time support.</param>
        /// <param name="unitId">The unit id.</param>
        /// <param name="dataType">Type of the data.</param>
        /// <param name="generalCategory">The general category.</param>
        /// <returns></returns>
        public override IEnumerable<ODM.Data.Model.Variable> GetVariables(string variableName, string sampleMedium, string valueType, double timeSupport, int timeSupportUnitId, string dataType, string generalCategory)
        {
            var result = from vb in this._data.Variables
                         select vb;

            if (!string.IsNullOrEmpty(variableName))
                result = result.Where(vb => vb.Name == variableName);

            result = result.Where(vb => vb.SampleMedium == sampleMedium);

            result = result.Where(vb => vb.ValueType == valueType);

            result = result.Where(vb => vb.TimeSupport == timeSupport);

            result = result.Where(vb => vb.TimeUnits.ID == timeSupportUnitId);

            result = result.Where(vb => vb.DataType == dataType);

            result = result.Where(vb => vb.GeneralCategory == generalCategory);

            result = from vb in result
                     orderby vb.Code
                     select vb;

            return GetVariables(result);
        }

        /// <summary>
        /// Gets the variables.
        /// </summary>
        /// <param name="siteCode">The site code.</param>
        /// <param name="siteName">Name of the site.</param>
        /// <returns></returns>
        public override IEnumerable<ODM.Data.Model.Variable> GetVariablesForSite(string siteCode, string siteName)
        {
            var seriesCatalog = from sc in this._data.SeriesCatalog
                                select sc;

            if (!string.IsNullOrEmpty(siteCode))
                seriesCatalog = seriesCatalog.Where(sc => sc.SiteCode == siteCode);

            if (!string.IsNullOrEmpty(siteName))
                seriesCatalog = seriesCatalog.Where(sc => sc.SiteName == siteName);

            var result = from sc in seriesCatalog
                         group sc by sc.Variable into g
                         select g.Key;

            return GetVariables(result);
        }

        public override ODM.Data.Model.Variable CreateVariable(ODM.Data.Model.Variable variable)
        {
            var id = (from v in this._data.Variables select v.ID).Max() + 1;

            var variableToAdd = Variable.CreateVariable(id, variable.Code, variable.Name, variable.SampleMedium, variable.ValueType, variable.IsRegular, variable.TimeSupport.Value, variable.DataType, variable.GeneralCategory, variable.NoDataValue, variable.Speciation);
            variableToAdd.Unit = (from u in this._data.Units where u.ID == variable.Unit.ID select u).First();
            variableToAdd.TimeUnits = (from u in this._data.Units where u.ID == variable.TimeSupport.ID select u).First();

            this._data.AddToVariables(variableToAdd);
            this._data.SaveChanges();

            return null;
        }

        public override ODM.Data.Model.Variable UpdateVariable(ODM.Data.Model.Variable value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteVariable(ODM.Data.Model.Variable value)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets the variables.
        /// </summary>
        /// <param name="result">The result.</param>
        /// <returns></returns>
        private IEnumerable<ODM.Data.Model.Variable> GetVariables(IQueryable<Variable> result)
        {
            var a = from r in result
                    orderby r.Code, r.Name
                    select new ODM.Data.Model.Variable
                    {
                        ID = r.ID,
                        Code = r.Code,
                        Name = r.Name,
                        DataType = r.DataType,
                        GeneralCategory = r.GeneralCategory,
                        SampleMedium = r.SampleMedium,
                        Speciation = r.Speciation,
                        IsRegular = r.IsRegular,
                        NoDataValue = r.NoDataValue,
                        TimeSupport = new ODM.Data.Model.UnitValue<double>
                        {
                            ID = r.TimeUnits.ID,
                            Name = r.TimeUnits.Name,
                            Abbreviation = r.TimeUnits.Abbreviation,
                            Type = r.TimeUnits.Type,
                            Value = r.TimeSupport,
                        },
                        Unit = new ODM.Data.Model.Unit
                        {
                            ID = r.Unit.ID,
                            Name = r.Unit.Name,
                            Abbreviation = r.Unit.Abbreviation,
                            Type = r.Unit.Type,
                        },
                        ValueType = r.ValueType,
                    };

            ObjectQuery query = a as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in a)
            {
                yield return item;
            }
        }

        #endregion

        #region Term operations

        public override ODM.Data.Model.Term GetTerm(int ID)
        {
            throw new NotImplementedException();
        }

        public override IEnumerable<ODM.Data.Model.Term> GetTerms(TermType termType, bool inUseOnly)
        {
            IQueryable<Term> result = null;
            
            switch (termType)
            {
                case TermType.None:
                    break;
                case TermType.CensorCode:
                    if (inUseOnly)
                    {
                        result = from t in this._data.CensorCodeTerms
                                 from n in
                                     (from dv in this._data.DataValues select dv.CensorCode).Distinct()
                                 where
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.CensorCodeTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    break;
                case TermType.DataType:
                    if (inUseOnly)
                    {
                        result = from t in this._data.DataTypeTerms
                                 from n in
                                     (from sc in this._data.SeriesCatalog select sc.DataType).Distinct()
                                 where
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.DataTypeTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    break;
                case TermType.SampleMedium:
                    if (inUseOnly)
                    {
                        result = from t in this._data.SampleMediumTerms
                                 from n in
                                     (from sc in this._data.SeriesCatalog select sc.SampleMedium).Distinct()
                                 where
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.SampleMediumTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    break;
                case TermType.Speciation:
                    if (inUseOnly)
                    {
                        result = from t in this._data.SpeciationTerms
                                 from n in
                                     (from sc in this._data.SeriesCatalog select sc.Speciation).Distinct()
                                 where
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.SpeciationTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    break;
                case TermType.TopicCategory:
                    if (inUseOnly)
                    {
                        result = from t in this._data.TopicCategoryTerms
                                 from n in
                                     (from sc in this._data.ISOMetadata select sc.TopicCategory).Distinct()
                                 where
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.TopicCategoryTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    break;
                case TermType.ValueType:
                    if (inUseOnly)
                    {
                        result = from t in this._data.ValueTypeTerms
                                 from n in
                                     (from sc in this._data.SeriesCatalog select sc.ValueType).Distinct()
                                 where
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.ValueTypeTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    break;
                case TermType.VariableName:
                    if (inUseOnly)
                    {
                        result = from t in this._data.VariableNameTerms
                                 from n in
                                     (from sc in this._data.SeriesCatalog select sc.VariableName).Distinct()
                                 where
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.VariableNameTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    break;
                case TermType.VerticalDatum:
                    if (inUseOnly)
                    {
                        result = from t in this._data.VerticalDatumTerms
                                 from n in
                                     (from sc in this._data.Sites select sc.VerticalDatum).Distinct()
                                 where
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.VerticalDatumTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }

                    break;
                case TermType.GeneralCategory:
                    if (inUseOnly)
                    {
                        result = from t in this._data.GeneralCategoryTerms
                                 from n in (from sc in this._data.SeriesCatalog select sc.GeneralCategory).Distinct()
                                 where 
                                    n == t.Term
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    else
                    {
                        result = from t in this._data.GeneralCategoryTerms
                                 select new ODM.Data.Model.Term
                                 {
                                     Name = t.Term,
                                     Definition = t.Definition,
                                 };
                    }
                    break;
                default:
                    break;
            }

            ObjectQuery query = result as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            foreach (var item in result)
            {
                yield return item;
            }

        }

        public override ODM.Data.Model.Term CreateTerm(TermType termType, ODM.Data.Model.Term value)
        {
            throw new NotImplementedException();
        }

        public override ODM.Data.Model.Term UpdateTerm(TermType termType, ODM.Data.Model.Term value)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteTerm(TermType termType, ODM.Data.Model.Term value)
        {
            throw new NotImplementedException();
        }

        #endregion
        
        #region Derive Data Series operations

        /// <summary>
        /// Creates the QC level1 DS values.
        /// </summary>
        /// <param name="siteID">The site ID.</param>
        /// <param name="varID">The var ID.</param>
        /// <param name="methodID">The method ID.</param>
        /// <param name="sourceID">The source ID.</param>
        /// <param name="qcLevelID">The qc level ID.</param>
        /// <returns></returns>
        public override ODM.Data.Model.SeriesCatalog CreateQualityControlLevelDataSeries(int seriesCatalogId, int siteID, int varID, int methodID, int sourceID, int qcLevelID)
        {
            var seriesCatalog = this._data.CreateQualityControlDataSeries(seriesCatalogId, siteID, varID, methodID, sourceID, qcLevelID).First();
            var seriesCatalogs = from sc in this._data.SeriesCatalog
                                 where sc.SeriesID == seriesCatalog.SeriesID
                                 select sc;

            return GetSeriesCatalog(seriesCatalogs).FirstOrDefault();
        }

        public override ODM.Data.Model.SeriesCatalog CreateSmoothedDataSeries(int seriesCatalogId, int siteID, int varID, int methodID, int sourceID, int qcLevelID, double noDataValue)
        {
            var dataValues = (from dv in this._data.DataValues
                              from sc in this._data.SeriesCatalog
                              where sc.SeriesID == seriesCatalogId
                                  && sc.Site.ID == dv.Site.ID
                                  && sc.Variable.ID == dv.Variable.ID
                                  && sc.QualityControlLevel.ID == dv.QualityControlLevel.ID
                                  && sc.Method.ID == dv.Method.ID
                                  && sc.Source.ID == dv.Source.ID
                              select dv).ToList();

            var temp = Smooth_LOWESS(dataValues.Count, dataValues, 10);


            throw new NotImplementedException();
            //'This function creates the new points for a QC Level1 Data Series
            //'Inputs:  siteID -> the siteID for the new Data Series (NOTE: value is stored for each point)
            //'         varID -> the variableID for the new Data Series (NOTE: value is stored for each point)
            //'         methodID -> the methodID for the new Data Series (NOTE: value is stored for each point)
            //'         sourceID -> the sourceID for the new Data Series (NOTE: value is stored for each point)
            //'         qcLevelID -> the QCLevelID for the new Data Series (NOTE: value is stored for each point)
            //'         noDataValue -> holds the NoDataValue for the new Data Series, used to see if should calculate a new value at the current point or not
            //'         valueCount (ByRef) -> the total number of points created, the input value is junk = value is set in this function and returned
            //'         beginDate (ByRef) -> the LocalBeginDate of the points created, the input value is junk = value is set in this function and returned
            //'         endDate (ByRef) -> the LocalEndDate of the points created, the input value is junk = value is set in this function and returned
            //'         beginDateUTC (ByRef) -> the UTCBeginDate of the points created, the input value is junk = value is set in this function and returned
            //'         endDateUTC (ByRef) -> the UTCEndDate of the points created, the input value is junk = value is set in this function and returned
            //'Outputs: Boolean -> tracks if the new points for the smoothed Data Series were created successfully or not
            //'         valueCount (ByRef) -> the total number of point created
            //'         beginDate (ByRef) -> the LocalDate of the first point
            //'         endDate (ByRef) -> the LocalDate of the last point
            //'         beginDateUTC (ByRef) -> the UTCDate of the first point
            //'         endDateUTC (ByRef) -> the UTCDate of the last point
            //Dim i As Integer 'counter
            //Dim numCreated As Integer = 0 'tracks the number of points created so far
            //Dim numVals As Integer = 0 'holds the number of values retrieved from the Database -> number of values in the Data Series deriving from
            //Dim valDT As System.Data.DataTable  'holds the set of values for the Data Series deriving from
            //'Dim maxQuery As String 'the sql query to pull the Maximum ValueID value from the Database -> so can start numbering higher than the highest value in the database to avoid duplicates
            //Dim maxDT As System.Data.DataTable  'the data table that holds the data retrieved from the database using maxQuery
            //Dim addQuery As String 'the sql query used to update the newly created values to the database
            //Dim addDT As System.Data.DataTable  'the data table (clone of valDT) that holds the newly created values
            //Dim newRow As System.Data.DataRow 'used to create/add new points (rows) to addDT
            //Dim lastID As Integer 'holds the maximum ValueID retrieved from the Database -> used to create the ValueIDs for the new points
            //Dim added As Boolean = False 'tracks if the new points were successfully updated to the database or not
            //Dim derivedFromID As Integer    'holds the DerivedFromID for the current point being created
            //Dim newVal As Double 'the new value for the current point creating
            //Dim curVal As Double 'the current value deriving from -> used to see if use no data value or if use new value for new point
            //Dim censorCode As String 'holds the NC censor code -> used to create a new point
            //Dim lowessY As Double() 'contains the lowess-smoothed method points
            //Dim numVals_percent As Integer
            //Try
            //    '1. Get the Data from the Database for the Data Series deriving from
            //    valDT = GetValuesFromDB("FromDSValues", m_DSFromIDs.SiteID, m_DSFromIDs.VariableID, m_DSFromIDs.QCLevelID, m_DSFromIDs.MethodID, m_DSFromIDs.SourceID)
            //    'validate that have data
            //    If (valDT Is Nothing) OrElse valDT.Rows.Count <= 0 Then
            //        'show error message
            //        MsgBox("Unable to retrieve the values for the Data Series deriving From:  Cannot continue!!  (unable to retrieve values)", MsgBoxStyle.Exclamation)
            //        'return False
            //        Exit Try
            //    End If
            //    numVals = valDT.Rows.Count
            //    numVals_percent = CInt(numVals * 0.125)

            //    ''2. Get the max ValueID value
            //    ''NOTE: There has to be values to derive from, so there have to be values in valDT = always get ID of last value
            //    'maxQuery = "SELECT MAX(" & db_fld_ValID & ") AS " & db_expr_MaxID & " FROM " & db_tbl_DataValues
            //    'maxDT = OpenTable("GetMaxValueID", maxQuery, g_CurrConnSettings)
            //    ''validate data
            //    'If (maxDT Is Nothing) OrElse maxDT.Rows.Count = 0 OrElse (maxDT.Rows(0).Item(db_expr_MaxID) Is DBNull.Value) Then
            //    '    'show error message 
            //    '    MsgBox("Unable to access the " & db_tbl_DataValues & " Table in the Database:  Cannot continue!!", MsgBoxStyle.Exclamation)
            //    '    'return false
            //    '    Exit Try
            //    'End If
            //    ''NOTE: There has to be values to derive from, so there has to be a value in maxDT = always get ID of first value
            //    'lastID = maxDT.Rows(0).Item(db_expr_MaxID)
            //    lastID = g_CurrDataSource.GetMaxValueID()

            //    '3. Create an empty data table to add values into
            //    addDT = valDT.Clone()

            //    '4. Get the new smoothed Values
            //    'NOTE: For now just using the LOWESS method
            //    lowessY = Smooth_LOWESS(numVals, valDT)
            //    If lowessY Is Nothing Then
            //        'no values were created, return false (NOTE: an error message is show later, so don't need a duplicate!!)
            //        Exit Try
            //    End If

            //    '5. Create the New Values
            //    censorCode = db_val_CCCVTerm_NULL
            //    For i = 0 To numVals - 1
            //        'get the current value (one to derive from)
            //        curVal = valDT.Rows(i).Item(db_fld_ValValue)

            //        'Calculate new value
            //        If curVal <> noDataValue Then
            //            newVal = lowessY(i)
            //        Else
            //            newVal = noDataValue
            //        End If

            //        'create the new point
            //        newRow = addDT.NewRow()
            //        newRow.Item(db_fld_ValID) = lastID + (i + 1)
            //        newRow.Item(db_fld_ValValue) = newVal
            //        newRow.Item(db_fld_ValDateTime) = valDT.Rows(i).Item(db_fld_ValDateTime)
            //        newRow.Item(db_fld_ValUTCOffset) = valDT.Rows(i).Item(db_fld_ValUTCOffset)
            //        newRow.Item(db_fld_ValUTCDateTime) = valDT.Rows(i).Item(db_fld_ValUTCDateTime)
            //        newRow.Item(db_fld_ValSiteID) = siteID
            //        newRow.Item(db_fld_ValVarID) = varID
            //        newRow.Item(db_fld_ValOffsetValue) = valDT.Rows(i).Item(db_fld_ValOffsetValue)
            //        newRow.Item(db_fld_ValOffsetTypeID) = valDT.Rows(i).Item(db_fld_ValOffsetTypeID)
            //        newRow.Item(db_fld_ValCensorCode) = censorCode 'NOTE: This value is set to 'nd' = not detected
            //        'NOTE: These values become NULL!!
            //        '***************************************************
            //        'newRow.Item(db_fld_ValQualifierID) = qualifierID
            //        '***************************************************
            //        newRow.Item(db_fld_ValMethodID) = methodID
            //        newRow.Item(db_fld_ValSourceID) = sourceID

            //        'NOTE: Per Jeff 4/4/07, just create a new DFID everytime!!
            //        '***********************************************************************************************
            //        'derivedFromID = GetDerivedFromIDFromDB(valDT.Rows(i).Item(db_fld_ValID))
            //        'If derivedFromID < 0 Then

            //        'try creating one
            //        derivedFromID = CreateNewDerivedFromIDInDB(valDT.Rows(i).Item(db_fld_ValID))
            //        If derivedFromID < 0 Then
            //            'leave it NULL
            //        Else
            //            'set the value
            //            newRow.Item(db_fld_ValDerivedFromID) = derivedFromID
            //        End If
            //        'Else
            //        '	'set the value
            //        '	newRow.Item(db_fld_ValDerivedFromID) = derivedFromID
            //        'End If
            //        '***********************************************************************************************

            //        newRow.Item(db_fld_ValQCLevel) = qcLevelID
            //        'add it to the dataTable
            //        addDT.Rows.Add(newRow)
            //        numCreated += 1
            //        If i Mod numVals_percent = 0 AndAlso i <> 0 Then
            //            'update progress bar
            //            g_FProgress.pbarProgress.Value += 10
            //            g_FProgress.pbarProgress.Refresh()
            //        End If
            //    Next i

            //    'update to 90%
            //    g_FProgress.pbarProgress.Value = 90
            //    g_FProgress.pbarProgress.Refresh()

            //    '5. Update Database w/ New Values
            //    addQuery = "SELECT * FROM " & db_tbl_DataValues & " ORDER BY " & db_fld_ValID
            //    added = UpdateTable(addDT, addQuery, g_CurrConnSettings.ConnectionString)
            //    If Not (added) Then
            //        'show an error message
            //        MsgBox("Unable to update the " & db_tbl_DataValues & " Table with the values for the new Data Series:  Cannot Continue!!", MsgBoxStyle.Exclamation)
            //        'return False
            //        Exit Try
            //    End If

            //    '6. Set Date Return Values
            //    beginDate = addDT.Rows(0).Item(db_fld_ValDateTime)
            //    endDate = addDT.Rows(numCreated - 1).Item(db_fld_ValDateTime)
            //    beginDateUTC = addDT.Rows(0).Item(db_fld_ValUTCDateTime)
            //    endDateUTC = addDT.Rows(numCreated - 1).Item(db_fld_ValUTCDateTime)

            //    '7. Release resources
            //    If Not (valDT Is Nothing) Then
            //        valDT.Dispose()
            //        valDT = Nothing
            //    End If
            //    'If Not (maxDT Is Nothing) Then
            //    '    maxDT.Dispose()
            //    '    maxDT = Nothing
            //    'End If
            //    If Not (addDT Is Nothing) Then
            //        addDT.Dispose()
            //        addDT = Nothing
            //    End If
            //    If Not (newRow Is Nothing) Then
            //        newRow = Nothing
            //    End If

            //    '8. return number of values created, True -> everything worked!!
            //    valueCount = numCreated
            //    Return True
            //Catch ex As Exception
            //    ShowError("An Error occurred while creating the Smoothed Values for New Data Series." & vbCrLf & "Message = " & ex.Message, ex)
            //End Try
            //'release resources
            //If Not (valDT Is Nothing) Then
            //    valDT.Dispose()
            //    valDT = Nothing
            //End If
            //If Not (maxDT Is Nothing) Then
            //    maxDT.Dispose()
            //    maxDT = Nothing
            //End If
            //If Not (addDT Is Nothing) Then
            //    addDT.Dispose()
            //    addDT = Nothing
            //End If
            //If Not (newRow Is Nothing) Then
            //    newRow = Nothing
            //End If
            //'errors occurred above, return 0 -> so looks like nothing was created!!
            //valueCount = 0
            //Return False
        }

        private IList<double> Smooth_LOWESS(int numVals, List<DataValue> valDT, double smoothWindow)
        {
            //Calculates new Y-values that have been smoothing using the LOWESS algorithm 
            //NOTE: Code for this was modified from code recieved from Brent Aulenbach
            //Inputs:  numVals -> the total number of values to be smoothed
            //         valDT -> the set of Values to smooth
            //Outputs: Double() -> the new set of Y-Values
            double curYVal = 0;
            //current Y-Value working with -> used to calculate diffSmoothY values
            double curXVal = 0;
            //current X-Value working with -> used to calculate rwlreg function
            //double smoothWindow_Days = 0;
            //smoothing window value in Days -> for rwlreg function
            List<double> newYVals = new List<double>(numVals);
            //set of newly calculated Y-values -> Return Value
            List<double> residuals = new List<double>(numVals);
            //set of residual values -> used to calculate the Weights in the Y-Value direction
            List<double> diffSmoothY = new List<double>();
            //set of |y - (smoothed y)| values -> used to calculate median value

            //System.Collections.ArrayList sortedDSY = default(System.Collections.ArrayList);
            List<double> sortedDSY = new List<double>();

            //sorted array of diffSmoothY values -> to get the correct Median value
            double median = 0;
            //the Median |y - (smoothed y)| value -> used to weight new value in the Y-Value direction
            double tempResidual = 0;
            //used to calculate the new Residual value
            double temp1_TRSquared = 0;
            //used to calculate the new residual value
            int medianIndex = 0;
            //index of the Median Value -> used to get the corrent Median value
            try
            {
                //1. Get smoothing window value, convert it into days from minutes
                var smoothWindow_Days = ((smoothWindow / 60) / 24);

                for (int i = 0; i < valDT.Count; i++)
                {
                    residuals = new List<double>();
                    for (int j = 0; j < valDT.Count; j++)
                    {
                        residuals.Add(1);
                    }
                    newYVals.Add(RWLReg(smoothWindow_Days, numVals, valDT[i].LocalDateTime.ToOADate(), residuals, valDT));
                }

                //3. make two iterations
                for (int i = 0; i <= 1; i++)
                {
                    //clear out values in sortedDSY -> only used to calculate Median, so needs to reset for each pass!!
                    sortedDSY.Clear();

                    for (int j = 0; j < valDT.Count; j++)
                    {
                        var val = Math.Abs(valDT[j].Value - newYVals[j]);
                        diffSmoothY.Add(val);
                        sortedDSY.Add(val);
                    }
                    //sort the values in sortedDSY
                    sortedDSY.Sort();

                    //find the Median value of all (|y - (smoothed y)|) values
                    medianIndex = ((numVals + 1) / 2) - 1;
                    //using 0-based arrays, so have to apply correct offset!!
                    //if ((Conversion.Int(numVals / 2) == Conversion.Int((numVals + 1) / 2)))
                    if (numVals % 2 == 0)
                    {
                        //n = even
                        median = (sortedDSY[medianIndex] + sortedDSY[medianIndex + 1]) / 2;
                    }
                    else
                    {
                        //n = odd
                        median = sortedDSY[medianIndex];
                    }

                    //Weight values in Y-Value direction (residuals)
                    for (int j = 0; j < valDT.Count; j++)
                    {
                        tempResidual = diffSmoothY[j] / (6 * median);
                        temp1_TRSquared = 1 - Math.Pow(tempResidual, 2);
                        if (temp1_TRSquared < 0 || double.IsNaN(temp1_TRSquared))
                        {
                            residuals[j] = 0;
                        }
                        else
                        {
                            residuals[j] = Math.Round(Math.Pow(temp1_TRSquared, 2), 5);
                        }
                    }
                    //weight in the xdirection and do weighted least squares
                    for (int j = 0; j < valDT.Count; j++)
                    {
                        newYVals[j] = RWLReg(smoothWindow_Days, numVals, valDT[j].LocalDateTime.ToOADate(), residuals, valDT);
                    }
                }

                //return the new set of YVals
                return newYVals;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(CultureInfo.CurrentCulture, "An Error occurred while running the LOWESS Smoothing routine.\nMessage = ", ex.Message), ex);
            }
            return null;
        }

        private double RWLReg(double smoothWindow, int numVals, double X_OADate, List<double> residuals, List<DataValue> valDT)
        {
            //this function is called by LOWESS
            //Robust Weighted Regression, tricube weight by dinstance on x-axis (fractional day), bisquare weight by distance on y-axis (residual) 
            //From: PT2-Trend2.f, DHELZEL
            //NOTE: Code for this was modified from code recieved from Brent Aulenbach
            //Inputs:  smoothWindow -> the smoothing window value in days
            //         numVals -> the total number of values smoothing
            //         X_OADate -> the X-value in OADate format to estimate the Y-Value for
            //         residuals -> the residual values (y-Value bisquare weightings) calculated in LOWESS routine -> used to calculate new X-Value weight
            //         valDT -> the set of actual X-Values -> used to calculate weights in X-Direction
            //Outputs: Double -> the estimated Y-Value at the given X-Value
            int i = 0;
            //counter
            //double newY = 0;
            //new estimated value -> return value
            List<double> weights = new List<double>();
            //set of weights -> used int WLSq function
            double curWeight = 0;
            //current X-Value weight
            double tempWeight = 0;
            //used to calculate weight
            //double halfWindow = 0;
            //half width of the window
            //double weightsTotal = 0;
            //running total of all the weights -> so can make weights sum to 1
            double curX_OADate = 0;
            //current X value in OADate format
            double diffX = 0;
            //the difference between x(i) and current X -> used to calculate X-Value weight
            double aVal = 0;
            //calculated in the WLSq function -> used to estimate new Y-Value
            double bVal = 0;
            //calculate in the WLSq function -> used to estimate new Y-Value
            try
            {
                //1. Initialize values
                var halfWindow = smoothWindow / 2;
                var weightsTotal = 0.0d;
                // ERROR: Not supported in C#: ReDimStatement


                //2. Weight in X-Value Direction (time)
                for (i = 0; i < valDT.Count; i++)
                {
                    //curX_OADate = ((DateTime)valDT.Rows[i]["LocalDateTime"]).ToOADate();
                    diffX = (valDT[i].LocalDateTime.ToOADate() - X_OADate) / halfWindow;
                    tempWeight = 1 - Math.Abs(Math.Pow(diffX, 3));
                    //1 - |(diffX ^ 3)|
                    if (tempWeight < 0)
                    {
                        curWeight = 0;
                    }
                    else
                    {
                        //tempWeight ^ 3
                        curWeight = Math.Pow(tempWeight, 3);
                    }
                    //set value in Weights
                    var weight = curWeight * residuals[i];
                    weights.Add(weight);
                    //update weightsTotal
                    weightsTotal += weight;
                }

                //3. make Weights sum to 1
                if (weightsTotal != 0)
                {
                    //make sure don't divide by 0
                    for (i = 0; i <= numVals - 1; i++)
                    {
                        curWeight = weights[i];
                        weights[i] = Math.Round(curWeight / weightsTotal, 5);
                    }
                }

                //4. calculate A,B values
                WLSq(ref aVal, ref bVal, numVals, weights, valDT);

                //5. Estimate new Y-Value
                var newY = Math.Round(aVal + (bVal * X_OADate), 5);

                //6. see if value = undefined
                if ((double.IsNaN(newY)) == true)
                {
                    //return 0 for now!! -> don't know what else to do!!
                    newY = 0;
                }
                return newY;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(CultureInfo.CurrentCulture, "An Error occurred while calculated the new Y-Value for the LOWESS smoothing routine.\nMessage = ", ex.Message), ex);
            }
        }

        private void WLSq(ref double aVal, ref double bVal, int numVals, List<double> weights, List<DataValue> valDT)
        {
            //this function is called by RWLReg
            //Calculates the Weighted Least Squares
            //From: PT2-Trend2.f, DHELZEL
            //NOTE: Code for this was modified from code recieved from Brent Aulenbach
            //Inputs:  aVal (ByRef) -> A value for the fitted line -> input value is junk, value is calculated here
            //         bVal (ByRef) -> B value for the fitted line -> input value is junk, value is calculated here
            //         numVals -> the total number of values smoothing
            //         weights -> the X-Value weights calculated in RWLReg
            //         valDT -> the original set of data -> used to calculate A,B values
            //int i = 0;
            //counter
            double weightXX = 0;
            //holds the running total of (curWeight * curX * curX) values -> used to calculate A,B values
            double weightX = 0;
            //holds the running total of (curWeight * curX) values -> used to calculate A,B values
            double weightXY = 0;
            //holds the running total of (curWeight * curX * curY) values -> used to calculate A,B values
            double weightY = 0;
            //holds the running total of (curWeight * curY) values -> used to calculate A,B values
            double curX_OADate = 0;
            //holds the current X value in OADate format -> used to calculate A,B values
            double curY = 0;
            //holds the actual current Y value -> used to calculate A,B values
            //double curWeight = 0;
            //holds the current weight value -> used to calculate A,B values
            double topVal = 0;
            //holds the top portion of the equation to calculate the B Value -> (weightXY - (weightY * weightX))/(weightXX - (weightX * weightX))
            double bottomVal = 0;
            //holds the bottom portion of the equation to calculate the B Value -> (weightXY - (weightY * weightX))/(weightXX - (weightX * weightX))
            try
            {
                //1. intialize values
                aVal = 0;
                bVal = 0;

                //2. calculate weightXX, weightX, weightXY, weightY totals
                for (int i = 0; i < weights.Count; i++)
                {
                    //get current weight
                    var curWeight = weights[i];
                    //if curWeight <> 0, then calculate -> otherwise, just adding 0 all the time -> allows shorter calc times!!
                    if (curWeight != 0)
                    {
                        //get current values (x,y)
                        curX_OADate = valDT[i].LocalDateTime.ToOADate();
                        curY = valDT[i].Value;
                        //add to totals
                        weightXX += (curWeight * Math.Pow(curX_OADate, 2));
                        weightX += (curWeight * curX_OADate);
                        weightXY += (curWeight * curX_OADate * curY);
                        weightY += (curWeight * curY);
                    }
                }

                //3. Calculate B Value
                topVal = Math.Round(weightXY - (weightY * weightX), 5);
                bottomVal = Math.Round(weightXX - (Math.Pow(weightX, 2)), 5);

                if (bottomVal == 0)
                {
                    bVal = 0;
                }
                else
                {
                    bVal = Math.Round(topVal / bottomVal, 5);
                }

                //4. Calculate A Value
                aVal = Math.Round(weightY - (bVal * weightX), 5);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(CultureInfo.CurrentCulture, "An Error occurred while calculate the Weighted Least Squares values (A,B Values) for the current value smoothing.\nMessage = ", ex.Message), ex);
                //errors occurred, reset A,B vals
                aVal = 0;
                bVal = 0;
            }
        }

        /// <summary>
        /// Creates the aggregate DS values.
        /// </summary>
        /// <param name="seriesCatalogId">The series catalog id.</param>
        /// <param name="aggMethod">The agg method.</param>
        /// <param name="siteID">The site ID.</param>
        /// <param name="varID">The var ID.</param>
        /// <param name="methodID">The method ID.</param>
        /// <param name="sourceID">The source ID.</param>
        /// <param name="qcLevelID">The qc level ID.</param>
        /// <param name="noDataValue">The no data value.</param>
        /// <returns></returns>
        public override ODM.Data.Model.SeriesCatalog CreateAggregateDataSeries(int seriesCatalogId, AggregateMethod method, int siteID, int varID, int methodID, int sourceID, int qcLevelID, double noDataValue)
        {
            var seriesCatalog = this._data.CreateAggregateDataSeries(seriesCatalogId, "nc", (int)method, noDataValue, siteID, varID, methodID, sourceID, qcLevelID).First();
            var seriesCatalogs = from sc in this._data.SeriesCatalog
                                 where sc.SeriesID == seriesCatalog.SeriesID
                                 select sc;

            return GetSeriesCatalog(seriesCatalogs).FirstOrDefault();
        }

        /// <summary>
        /// Creates the algebraic DS values.
        /// </summary>
        /// <param name="seriesCatalogId">The series catalog id.</param>
        /// <param name="a">A.</param>
        /// <param name="b">The b.</param>
        /// <param name="c">The c.</param>
        /// <param name="d">The d.</param>
        /// <param name="e">The e.</param>
        /// <param name="f">The f.</param>
        /// <param name="siteID">The site ID.</param>
        /// <param name="varID">The var ID.</param>
        /// <param name="methodID">The method ID.</param>
        /// <param name="sourceID">The source ID.</param>
        /// <param name="qcLevelID">The qc level ID.</param>
        /// <param name="noDataValue">The no data value.</param>
        /// <returns></returns>
        public override ODM.Data.Model.SeriesCatalog CreateAlgebraicDataSeries(int seriesCatalogId, double a, double b, double c, double d, double e, double f, int siteID, int varID, int methodID, int sourceID, int qcLevelID, double noDataValue)
        {
            var seriesCatalog = this._data.CreateAlgebraicDataSeries(seriesCatalogId, a, b, c, d, e, f, "nc", noDataValue, siteID, varID, methodID, sourceID, qcLevelID).First();
            var seriesCatalogs = from sc in this._data.SeriesCatalog
                                 where sc.SeriesID == seriesCatalog.SeriesID
                                 select sc;

            return GetSeriesCatalog(seriesCatalogs).FirstOrDefault();
        }

        #endregion

        #region Export functions

        /// <summary>
        /// Exports the data values.
        /// </summary>
        /// <param name="seriesCatalogs">The series catalogs.</param>
        /// <param name="exportOptions">The export options.</param>
        /// <returns></returns>
        public override TextReader ExportDataValues(IEnumerable<ODM.Data.Model.SeriesCatalog> seriesCatalogs, ExportOptions exportOptions)
        {
            //  TODO:   Rework this method to make reading data from database and push it to the stream more efficient, for example introduce custom internal TextReader class
            StringBuilder report = new StringBuilder();

            var seriesCatalog = from sc in this._data.SeriesCatalog select sc;

            seriesCatalog = seriesCatalog.Where((Expression<Func<SeriesCatalog, int, bool>>)(((item, value) => item.SeriesID == value)), (from sc in seriesCatalogs select sc.ID).ToList());

            var result = from dv in this._data.DataValues
                         from sc in seriesCatalog
                         where
                            dv.Variable.ID == sc.Variable.ID
                            && dv.Source.ID == sc.Source.ID
                            && dv.Method.ID == sc.Method.ID
                            && dv.QualityControlLevel.ID == sc.QualityControlLevel.ID
                            && dv.Site.ID == sc.Site.ID
                         orderby sc.SeriesID
                         select new ExportRecord
                         {
                             ID = dv.ID,
                             SeriesID = sc.SeriesID,
                             Value = dv.Value,
                             ValueAccuracy = dv.ValueAccuracy,
                             LocalDateTime = dv.LocalDateTime,
                             OffsetValue = dv.OffsetValue,
                             OffsetTypeID = dv.OffsetType.ID,
                             CensorCode = dv.CensorCode,
                             QualifierID = dv.Qualifier.ID,
                             SiteCode = dv.Site.Code,
                             VariableCode = dv.Variable.Code,
                             SampleID = dv.Sample.ID,
                             DateTimeUTC = dv.DateTimeUTC,
                             UTCOffset = dv.UTCOffset,
                             SiteName = dv.Site.Name,
                             SiteLatitude = dv.Site.Latitude,
                             SiteLongitude = dv.Site.Longitude,
                             SrsName = dv.Site.DatumReferences.SrsName,
                             VariableName = dv.Variable.Name,
                             VariableUnitName = dv.Variable.Unit.Name,
                             VariableUnitAbbr = dv.Variable.Unit.Abbreviation,
                             VariableSampleMedium = dv.Variable.SampleMedium,
                             VariableSpeciation = dv.Variable.Speciation,
                             QualifierCode = dv.Qualifier.Code,
                             QualifierDescription = dv.Qualifier.Description,
                             OffsetDescription = dv.OffsetType.Description,
                             OffsetUnitName = dv.OffsetType.Unit.Name,
                             SourceOrganization = dv.Source.Organization,
                             SourceDescription = dv.Source.Description,
                             SourceCitation = dv.Source.Citation,
                             QualityControlLevelCode = dv.QualityControlLevel.Code,
                             QualityControlLevelDefinition = dv.QualityControlLevel.Definition,
                             QualityControlLevelExplanation = dv.QualityControlLevel.Explanation,
                         };


            ObjectQuery query = result as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            if (exportOptions.IncludeHeader)
            {
                report.Append(ExportRecord.GetHeaderString(exportOptions));
            }

            foreach (var dataValue in result)
            {
                report.Append(dataValue.GetFormattedString(exportOptions));
            }

            return new StringReader(report.ToString());

        }

        /// <summary>
        /// Exports the metadata.
        /// </summary>
        /// <param name="seriesCatalogs">The series catalogs.</param>
        /// <returns></returns>
        public override TextReader ExportMetadata(IEnumerable<ODM.Data.Model.SeriesCatalog> seriesCatalogs)
        {
            var series = from sc in this._data.SeriesCatalog
                         select sc;

            series = series.Where((Expression<Func<SeriesCatalog, int, bool>>)(((item, value) => item.SeriesID == value)), (from sc in seriesCatalogs select sc.ID).ToList());

            var result = from sc in series
                         orderby sc.SeriesID
                         select new
                         {
                             SeriesID = sc.SeriesID,
                             GeneralInformation = new
                             {
                                 TopicCategory = sc.Source.ISOMetadata.TopicCategory,
                                 Title = sc.Source.ISOMetadata.Title,
                                 Abstract = sc.Source.ISOMetadata.Abstract,
                                 ProfileVersion = sc.Source.ISOMetadata.ProfileVersion,
                                 MetadataLink = sc.Source.ISOMetadata.MetadataLink,
                                 MetadataCreationDate = DateTime.Now,
                             },
                             SiteInformation = new
                             {
                                 SiteCode = sc.Site.Code,
                                 SiteName = sc.Site.Name,
                                 GeographicCoordinates = new
                                 {
                                     Latitude = sc.Site.Latitude,
                                     Longitude = sc.Site.Longitude,
                                     SRSID = sc.Site.DatumReferences.SrsID,
                                     SRSName = sc.Site.DatumReferences.SrsName,
                                     IsGeographic = sc.Site.DatumReferences.IsGeographic,
                                     Notes = sc.Site.Code,
                                 },
                                 LocalCoordinates = new
                                 {
                                     LocalX = sc.Site.LocalX,
                                     LocalY = sc.Site.LocalY,
                                     PosAccuracy = sc.Site.PosAccuracy,
                                     SRSID = sc.Site.LocalProjectionReference.SrsID,
                                     SRSName = sc.Site.LocalProjectionReference.SrsName,
                                     IsGeographic = sc.Site.LocalProjectionReference.IsGeographic,
                                     Notes = sc.Site.LocalProjectionReference.Notes,
                                     Elevation = sc.Site.Elevation,
                                     VerticalDatum = sc.Site.VerticalDatum,
                                 },
                                 State = sc.Site.State,
                                 County = sc.Site.County,
                                 Comments = sc.Site.Comments,
                             },
                             VariableInformation = new
                             {
                                 VariableCode = sc.Variable.Code,
                                 VariableName = sc.Variable.Name,
                                 Speciation = sc.Variable.Speciation,
                                 VariableUnits = new
                                 {
                                     UnitsName = sc.Variable.Unit.Name,
                                     UnitsType = sc.Variable.Unit.Type,
                                     UnitsAbbreviation = sc.Variable.Unit.Abbreviation,
                                 },
                             },
                             MethodInformation = new
                             {
                                 MethodDescription = sc.Method.Description,
                                 MethodLink = sc.Method.Link,
                             },
                             SourceInformation = new
                             {
                                 Organization = sc.Source.Organization,
                                 SourceDescription = sc.Source.Description,
                                 SourceLink = sc.Source.Link,
                                 Contact = new
                                 {
                                     ContactName = sc.Source.ContactName,
                                     Phone = sc.Source.Phone,
                                     Email = sc.Source.Email,
                                     Address = sc.Source.Address,
                                     City = sc.Source.City,
                                     State = sc.Source.State,
                                     ZipCode = sc.Source.ZipCode,
                                 },
                             },
                             QualityControlLevelInformation = new
                             {
                                 QualityControlLevelCode = sc.QualityControlLevel.Code,
                                 Definition = sc.QualityControlLevel.Definition,
                                 Explanation = sc.QualityControlLevel.Explanation,
                             },
                         };

            var offsets = from of in
                              (from sc in series
                               from dv in this._data.DataValues
                               where
                                 sc.Site.ID == dv.Site.ID
                                 && sc.Variable.ID == dv.Variable.ID
                                 && sc.Method.ID == dv.Method.ID
                                 && sc.Source.ID == dv.Source.ID
                                 && sc.QualityControlLevel.ID == dv.QualityControlLevel.ID
                               select new
                               {
                                   SeriesID = sc.SeriesID,
                                   OffsetTypeID = (int?)dv.OffsetType.ID,
                                   OffsetDescription = dv.OffsetType.Description,
                                   OffsetUnitsName = dv.OffsetType.Unit.Name,
                                   OffsetUnitsType = dv.OffsetType.Unit.Type,
                                   OffsetUnitsAbbreviation = dv.OffsetType.Unit.Abbreviation,
                               })
                          group of by of into g
                          select g.Key;

            var qulifiers = from of in
                                (from sc in series
                                 from dv in this._data.DataValues
                                 where
                                   sc.Site.ID == dv.Site.ID
                                   && sc.Variable.ID == dv.Variable.ID
                                   && sc.Method.ID == dv.Method.ID
                                   && sc.Source.ID == dv.Source.ID
                                   && sc.QualityControlLevel.ID == dv.QualityControlLevel.ID
                                 select new
                                 {
                                     SeriesID = sc.SeriesID,
                                     QualifierID = (int?)dv.Qualifier.ID,
                                     QualifierCode = dv.Qualifier.Code,
                                     QualifierDescription = dv.Qualifier.Description,
                                 })
                            group of by of into g
                            select g.Key;

            var samples = from sp in
                              (from sc in series
                               from dv in this._data.DataValues
                               where
                                 sc.Site.ID == dv.Site.ID
                                 && sc.Variable.ID == dv.Variable.ID
                                 && sc.Method.ID == dv.Method.ID
                                 && sc.Source.ID == dv.Source.ID
                                 && sc.QualityControlLevel.ID == dv.QualityControlLevel.ID
                               select new
                               {
                                   SeriesID = sc.SeriesID,
                                   SampleID = (int?)dv.Sample.ID,
                                   SampleType = dv.Sample.Type,
                                   LabSampleCode = dv.Sample.LaboratoryCode,
                                   LabMethodID = (int?)dv.Sample.LabMethod.ID,
                               })
                          group sp by sp into g
                          select g.Key;

            var labMethods = from lm in
                                 (from sc in series
                                  from dv in this._data.DataValues
                                  where
                                    sc.Site.ID == dv.Site.ID
                                    && sc.Variable.ID == dv.Variable.ID
                                    && sc.Method.ID == dv.Method.ID
                                    && sc.Source.ID == dv.Source.ID
                                    && sc.QualityControlLevel.ID == dv.QualityControlLevel.ID
                                  select new
                                  {
                                      SeriesID = sc.SeriesID,
                                      LabMethodID = (int?)dv.Sample.LabMethod.ID,
                                      LabName = dv.Sample.LabMethod.LaboratoryName,
                                      LabOrganization = dv.Sample.LabMethod.LaboratoryOrganization,
                                      LabMethodName = dv.Sample.LabMethod.Name,
                                      LabMethodDescription = dv.Sample.LabMethod.Description,
                                      LabMethodLink = dv.Sample.LabMethod.Link,
                                  })
                             group lm by lm into g
                             select g.Key;


            ObjectQuery query = result as ObjectQuery;
            query.MergeOption = MergeOption.NoTracking;
            query.EnablePlanCaching = true;

            var metadata = result.ToList();

            XElement xmlRoot = new XElement("Metadata",
                                    new XElement("DataSeriesList",
                                        new XAttribute("Total", seriesCatalogs.Count()),
                                        (from ds in metadata
                                         select
                                             new XElement("DataSeries",
                                                 new XAttribute("ID", ds.SeriesID),
                                                 new XElement("GeneralInformation",
                                                 new XElement("TopicCategory", ds.GeneralInformation.TopicCategory),
                                                 new XElement("Title", ds.GeneralInformation.Title),
                                                 new XElement("Abstract", ds.GeneralInformation.Abstract),
                                                 new XElement("ProfileVersion", ds.GeneralInformation.ProfileVersion),
                                                 new XElement("MetadataLink", ds.GeneralInformation.MetadataLink),
                                                 new XElement("MetadataCreationDate", ds.GeneralInformation.MetadataCreationDate)
                                                 ),
                                                 new XElement("SiteInformation",
                                                     new XElement("SiteCode", ds.SiteInformation.SiteCode),
                                                     new XElement("SiteName", ds.SiteInformation.SiteName),
                                                     new XElement("GeographicCoordinates",
                                                         new XElement("Latitude", ds.SiteInformation.GeographicCoordinates.Latitude),
                                                         new XElement("Longitude", ds.SiteInformation.GeographicCoordinates.Longitude),
                                                         new XElement("SRSID", ds.SiteInformation.GeographicCoordinates.SRSID),
                                                         new XElement("SRSName", ds.SiteInformation.GeographicCoordinates.SRSName),
                                                         new XElement("IsGeographic", ds.SiteInformation.GeographicCoordinates.IsGeographic),
                                                         new XElement("Notes", ds.SiteInformation.GeographicCoordinates.Notes)
                                                 ),
                                                    new XElement("LocalCoordinates",
                                                         new XElement("LocalX", ds.SiteInformation.LocalCoordinates.LocalX),
                                                         new XElement("LocalY", ds.SiteInformation.LocalCoordinates.LocalY),
                                                         new XElement("PosAccuracy_m", ds.SiteInformation.LocalCoordinates.PosAccuracy),
                                                         new XElement("SRSID", ds.SiteInformation.LocalCoordinates.SRSID),
                                                         new XElement("SRSName", ds.SiteInformation.LocalCoordinates.SRSName),
                                                         new XElement("IsGeographic", ds.SiteInformation.LocalCoordinates.IsGeographic),
                                                         new XElement("Notes", ds.SiteInformation.LocalCoordinates.Notes),
                                                         new XElement("Elevation_m", ds.SiteInformation.LocalCoordinates.Elevation),
                                                         new XElement("VerticalDatum", ds.SiteInformation.LocalCoordinates.VerticalDatum)
                                                 ),
                                                     new XElement("State", ds.SiteInformation.State),
                                                     new XElement("County", ds.SiteInformation.County),
                                                     new XElement("Comments", ds.SiteInformation.Comments)
                                                 ),
                                                 new XElement("VariableInformation",
                                                     new XElement("VariableCode", ds.VariableInformation.VariableCode),
                                                     new XElement("VariableName", ds.VariableInformation.VariableName),
                                                     new XElement("Speciation", ds.VariableInformation.Speciation),
                                                     new XElement("VariableUnits",
                                                         new XElement("UnitsName", ds.VariableInformation.VariableUnits.UnitsName),
                                                         new XElement("UnitsType", ds.VariableInformation.VariableUnits.UnitsType),
                                                         new XElement("UnitsAbbreviation", ds.VariableInformation.VariableUnits.UnitsAbbreviation)
                                                    )
                                                 ),
                                                 new XElement("MethodInformation",
                                                     new XElement("MethodDescription", ds.MethodInformation.MethodDescription),
                                                     new XElement("MethodLink", ds.MethodInformation.MethodLink)
                                                 ),
                                                 new XElement("SourceInformation",
                                                     new XElement("Organization", ds.SourceInformation.Organization),
                                                     new XElement("SourceDescription", ds.SourceInformation.SourceDescription),
                                                     new XElement("SourceLink", ds.SourceInformation.SourceLink),
                                                     new XElement("Contact",
                                                         new XElement("ContactName", ds.SourceInformation.Contact.ContactName),
                                                         new XElement("Phone", ds.SourceInformation.Contact.Phone),
                                                         new XElement("Email", ds.SourceInformation.Contact.Email),
                                                         new XElement("Address", ds.SourceInformation.Contact.Address),
                                                         new XElement("City", ds.SourceInformation.Contact.City),
                                                         new XElement("State", ds.SourceInformation.Contact.State),
                                                         new XElement("ZipCode", ds.SourceInformation.Contact.ZipCode)
                                                        )
                                                    ),
                                                 new XElement("QualityControlLevelInformation",
                                                     new XElement("QualityControlLevelCode", ds.QualityControlLevelInformation.QualityControlLevelCode),
                                                     new XElement("Definition", ds.QualityControlLevelInformation.Definition),
                                                     new XElement("Explanation", ds.QualityControlLevelInformation.Explanation)
                                                 ),
                                                 new XElement("OffsetInformation",
                                                 (from offset in
                                                      (from of in offsets where ds.SeriesID == of.SeriesID && of.OffsetTypeID != null select of).ToList()
                                                  select new XElement("Offset",
                                                      new XAttribute("ID", offset.OffsetTypeID),
                                                      new XElement("OffsetDescription", offset.OffsetDescription),
                                                      new XElement("OffsetUnits",
                                                         new XElement("UnitsName", offset.OffsetUnitsName),
                                                         new XElement("UnitsType", offset.OffsetUnitsType),
                                                         new XElement("UnitsAbbreviation", offset.OffsetUnitsAbbreviation)
                                                         )
                                                     )
                                                    ).ToArray()
                                                 ),
                                                 new XElement("QualifierInformation",
                                                    (from qulifier in
                                                         (from qf in qulifiers where ds.SeriesID == qf.SeriesID && qf.QualifierID != null select qf).ToList()
                                                     select new XElement("Qualifier",
                                                         new XAttribute("ID", qulifier.QualifierID),
                                                         new XElement("QualifierCode", qulifier.QualifierCode),
                                                         new XElement("QualifierDescription", qulifier.QualifierDescription)
                                                    )
                                                    ).ToArray()
                                                 ),
                                                 new XElement("SampleInformation",
                                                    (from sample in
                                                         (from s in samples where ds.SeriesID == s.SeriesID && s.SampleID != null select s).ToList()
                                                     select new XElement("Sample",
                                                         new XAttribute("ID", sample.SampleID),
                                                         new XElement("SampleType", sample.SampleType),
                                                         new XElement("LabSampleCode", sample.LabSampleCode),
                                                         new XElement("LabMethodID", sample.LabMethodID)
                                                    )
                                                    ).ToArray()
                                                 ),
                                                 new XElement("LabMethodInformation",
                                                    (from labMethod in
                                                         (from lm in labMethods where ds.SeriesID == lm.SeriesID && lm.LabMethodID != null select lm).ToList()
                                                     select new XElement("LabMethod",
                                                         new XAttribute("ID", labMethod.LabMethodID),
                                                         new XElement("LabName", labMethod.LabName),
                                                         new XElement("LabOrganization", labMethod.LabOrganization),
                                                         new XElement("LabMethodName", labMethod.LabMethodName),
                                                         new XElement("LabMethodDescription", labMethod.LabMethodDescription),
                                                         new XElement("LabMethodLink", labMethod.LabMethodLink)
                                                    )
                                                    ).ToArray()
                                                 )
                                            )
                                        ).ToArray()
                                    )
                                );

            return new StringReader(xmlRoot.ToString());
        }

        /// <summary>
        /// Helper class which represents exported record
        /// </summary>
        private class ExportRecord
        {
            public int ID { get; set; }
            public int SeriesID { get; set; }
            public double Value { get; set; }
            public double? ValueAccuracy { get; set; }
            public DateTime LocalDateTime { get; set; }
            public double? OffsetValue { get; set; }
            public int? OffsetTypeID { get; set; }
            public string CensorCode { get; set; }
            public int? QualifierID { get; set; }
            public string SiteCode { get; set; }
            public string VariableCode { get; set; }
            public int? SampleID { get; set; }
            public DateTime DateTimeUTC { get; set; }
            public double UTCOffset { get; set; }
            public string SiteName { get; set; }
            public double SiteLatitude { get; set; }
            public double SiteLongitude { get; set; }
            public string SrsName { get; set; }
            public string VariableName { get; set; }
            public string VariableUnitName { get; set; }
            public string VariableUnitAbbr { get; set; }
            public string VariableSampleMedium { get; set; }
            public string VariableSpeciation { get; set; }
            public string QualifierCode { get; set; }
            public string QualifierDescription { get; set; }
            public string OffsetDescription { get; set; }
            public string OffsetUnitName { get; set; }
            public string SourceOrganization { get; set; }
            public string SourceDescription { get; set; }
            public string SourceCitation { get; set; }
            public string QualityControlLevelCode { get; set; }
            public string QualityControlLevelDefinition { get; set; }
            public string QualityControlLevelExplanation { get; set; }

            internal static string GetHeaderString(ExportOptions exportOptions)
            {
                StringBuilder report = new StringBuilder();

                report.AppendFormat("{0}{1}{0}", exportOptions.EnclosedByCharacter, "ID");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SeriesID");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "Value");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "ValueAccuracy");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "LocalDateTime");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "OffsetValue");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "OffsetTypeID");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "CensorCode");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "QualifierID");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SiteCode");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "VariableCode");
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SampleID");

                if (exportOptions.ExportTime)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "DateTimeUTC");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "UTCOffset");
                }

                if (exportOptions.ExportSite)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SiteName");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SiteLatitude");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SiteLongitude");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SrsName");
                }

                if (exportOptions.ExportVariable)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "VariableName");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "VariableUnitName");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "VariableUnitAbbr");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "VariableSampleMedium");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "VariableSpeciation");
                }

                if (exportOptions.ExportQualifier)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "QualifierCode");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "QualifierDescription");
                }

                if (exportOptions.ExportOffset)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "OffsetDescription");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "OffsetUnitName");
                }

                if (exportOptions.ExportSource)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SourceOrganization");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SourceDescription");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "SourceCitation");
                }

                if (exportOptions.ExportQualityControlLevels)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "QualityControlLevelCode");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "QualityControlLevelDefinition");
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, "QualityControlLevelExplanation");
                }

                report.Append(exportOptions.EndOfLine);

                return report.ToString();
            }

            internal string GetFormattedString(ExportOptions exportOptions)
            {
                StringBuilder report = new StringBuilder();

                report.AppendFormat("{0}{1}{0}", exportOptions.EnclosedByCharacter, this.ID);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SeriesID);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.Value);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.ValueAccuracy);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.LocalDateTime);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.OffsetValue);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.OffsetTypeID);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.CensorCode);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.QualifierID);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SiteCode);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.VariableCode);
                report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SampleID);

                if (exportOptions.ExportTime)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.DateTimeUTC);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.UTCOffset);
                }

                if (exportOptions.ExportSite)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SiteName);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SiteLatitude);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SiteLongitude);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SrsName);
                }

                if (exportOptions.ExportVariable)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.VariableName);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.VariableUnitName);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.VariableUnitAbbr);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.VariableSampleMedium);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.VariableSpeciation);
                }

                if (exportOptions.ExportQualifier)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.QualifierCode);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.QualifierDescription);
                }

                if (exportOptions.ExportOffset)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.OffsetDescription);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.OffsetUnitName);
                }

                if (exportOptions.ExportSource)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SourceOrganization);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SourceDescription);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.SourceCitation);
                }

                if (exportOptions.ExportQualityControlLevels)
                {
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.QualityControlLevelCode);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.QualityControlLevelDefinition);
                    report.AppendFormat("{0}{1}{2}{1}", exportOptions.Delimiter, exportOptions.EnclosedByCharacter, this.QualityControlLevelExplanation);
                }

                report.Append(exportOptions.EndOfLine);

                return report.ToString();
            }

        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            if (this._data != null)
            {
                this._data.Dispose();
            }
        }

        #endregion
    }
}
